package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;
import model.common.LoggingNewGoods;

public class LoggingNewGoodsDao {
   
	public void addNewGoods(LoggingNewGoods lng) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+"insert into tab_loggingnewgoods " + "(goodsName,goodsUnitPrice,goodsPrice,goodsNumber,"
				    + " goodsUnitSum,goodsManufacturer,goodsProductionDate,goodsExpirationDate,goodsStockDate) "
				    + " values(?,?,?,?,?,?,?,?,?)  ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, lng.getGoodsName());
		ptmt.setDouble(2, lng.getGoodsUnitPrice());
		ptmt.setDouble(3, lng.getGoodsPrice());
		ptmt.setInt(4, lng.getGoodsNumber());
		ptmt.setDouble(5, lng.getGoodsUnitSum());
		ptmt.setString(6, lng.getGoodsManufacturer());
		ptmt.setDate(7, new java.sql.Date(lng.getGoodsProductionDate().getTime()));
		ptmt.setDate(8, new java.sql.Date(lng.getGoodsExpirationDate().getTime()));
		ptmt.setDate(9, new java.sql.Date(lng.getGoodsStockDate().getTime()));
		
		
		ptmt.execute();
		/*ptmt.close();
		conn.close();*/
	}
	
	public void deleteNewGoods(Integer goodsId) throws SQLException {
		Connection conn = DBHelper.getConnection();   
		String sql = " delete from tab_loggingnewgoods  where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ptmt.execute();
		/*ptmt.close();
		conn.close();*/
	}

	public void updateNewGoods(LoggingNewGoods lng) throws SQLException{
		Connection conn=DBHelper.getConnection();
		String sql=" update tab_loggingnewgoods set goodsName=?,goodsUnitPrice=?,goodsPrice=?,"
		+ " goodsNumber=?,goodsUnitSum=?,goodsManufacturer=?,goodsProductionDate=?,goodsExpirationDate=?,goodsStockDate=?"
				+ " where goodsId=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		ptmt.setString(1, lng.getGoodsName());
		ptmt.setDouble(2, lng.getGoodsUnitPrice());
		ptmt.setDouble(3, lng.getGoodsPrice());
		ptmt.setInt(4, lng.getGoodsNumber());
		ptmt.setDouble(5, lng.getGoodsUnitSum());
		ptmt.setString(6, lng.getGoodsManufacturer());
		ptmt.setDate(7, new java.sql.Date(lng.getGoodsProductionDate().getTime()));
		ptmt.setDate(8, new java.sql.Date(lng.getGoodsExpirationDate().getTime()));
		ptmt.setDate(9, new java.sql.Date(lng.getGoodsStockDate().getTime()));
		ptmt.setInt(10, lng.getGoodsId());
		
		ptmt.execute();
	/*	ptmt.close();
		conn.close();*/
	}
	
	public LoggingNewGoods get(Integer goodsId) throws SQLException {
		LoggingNewGoods lng = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_loggingnewgoods where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			lng = new LoggingNewGoods();
			lng.setGoodsId(rs.getInt("goodsStockId"));
			lng.setGoodsName(rs.getString("goodsName"));
			lng.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			lng.setGoodsPrice(rs.getDouble("goodsPrice"));
			lng.setGoodsNumber(rs.getInt("goodsNumber"));
			lng.setGoodsUnitSum(rs.getDouble("goodsSum"));
			lng.setGoodsManufacturer(rs.getString("goodsManufacturer"));
			lng.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			lng.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));		
			lng.setGoodsStockDate(rs.getDate("goodsStockDate"));
		}
		
		rs.close();
		ptmt.close();
		conn.close();
		return lng;
		
	}
	
	public List<LoggingNewGoods> query(List<Map<String, Object>> params) throws SQLException{
		List<LoggingNewGoods> result=new ArrayList<LoggingNewGoods>(); 
		Connection conn = DBHelper.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("select * from tab_loggingnewgoods where 1=1 ");
		if(params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String, Object> map=params.get(i);
				sb.append(" and " +map.get("name")+"  " +map.get("rela")+"  " +map.get("value"));
			}
		}
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		LoggingNewGoods lng = null;
		while (rs.next()) {
		lng = new LoggingNewGoods();
		lng.setGoodsId(rs.getInt("goodsId"));
		lng.setGoodsName(rs.getString("goodsName"));
		lng.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
		lng.setGoodsPrice(rs.getDouble("goodsPrice"));
		lng.setGoodsNumber(rs.getInt("goodsNumber"));
		lng.setGoodsUnitSum(rs.getDouble("goodsUnitSum"));
		lng.setGoodsManufacturer(rs.getString("goodsManufacturer"));
		lng.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
		lng.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
		lng.setGoodsStockDate(rs.getDate("goodsStockDate"));
			result.add(lng);
		}
		/*rs.close();
		ptmt.close();
		conn.close();*/
		return result;
		}
	
	public List<LoggingNewGoods> query() throws Exception{
		List<LoggingNewGoods> result=new ArrayList<LoggingNewGoods>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select goodsId,goodsName,goodsUnitSum from tab_loggingnewgoods ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		
		ResultSet rs=ptmt.executeQuery();
		
		LoggingNewGoods lng=null;
		while(rs.next()){
			lng=new LoggingNewGoods();
			lng.setGoodsId(rs.getInt("goodsId"));
			lng.setGoodsName(rs.getString("goodsName"));
			lng.setGoodsUnitSum(rs.getDouble("goodsUnitSum"));
			result.add(lng);
		}
		rs.close();
		ptmt.close();
		conn.close();
		return result;
	}
	
	public List<LoggingNewGoods> query(String goodsName,String goodsPrice) throws Exception{
		List<LoggingNewGoods> result=new ArrayList<LoggingNewGoods>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from tab_loggingnewgoods  ");
		
		sb.append(" where goodsName like ? and goodsPrice like ? ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		ptmt.setString(1, "%"+goodsName+"%");
		ptmt.setString(2, "%"+goodsPrice+"%");
		
		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		LoggingNewGoods lng=null;
		while(rs.next()){
			lng=new LoggingNewGoods();
			lng.setGoodsId(rs.getInt("goodsId"));
			lng.setGoodsName(rs.getString("goodsName"));
			lng.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			lng.setGoodsPrice(rs.getDouble("goodsPrice"));
			lng.setGoodsNumber(rs.getInt("goodsNumber"));
			lng.setGoodsUnitSum(rs.getDouble("goodsUnitSum"));
			lng.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			lng.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
			lng.setGoodsStockDate(rs.getDate("goodsStockDate"));
			result.add(lng);
		}
		rs.close();
		ptmt.close();
		conn.close();
		return result;
		
	}
	


	
}
